package blancosMikel.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;

import javax.jws.Oneway;

import blancosMikel.controller.beans.productosBeans;

public class productosDAO extends SigaabmDAO {
	StringBuilder query = new StringBuilder();

	public LinkedList<String> nombreProductos(){
		LinkedList<String> lista = new LinkedList<>();
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT nombre FROM ").append("productos").append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			while (rs.next()){
				lista.add(rs.getString("nombre"));
			}
		} catch (SQLException sql) {
		}finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return lista;	
	}
	
	public ArrayList<productosBeans> consultaTodoProducto(){
		ArrayList<productosBeans> lista = new ArrayList<productosBeans>();
		productosBeans bean;
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT * FROM ").append("productos").append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			while (rs.next()){
				bean = new productosBeans();
				bean.setId_productos(rs.getInt("id_productos"));
				bean.setId_subcategorias(rs.getInt("id_subcategorias"));
				bean.setNombre(rs.getString("nombre"));
				bean.setUrl(rs.getString("url"));
				lista.add(bean);
			}
		} catch (SQLException sql) {
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return lista;	
	}
	
	public productosBeans consultaCategoria(int id){
		productosBeans bean = null;
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT * FROM ").append("productos")
			.append(" WHERE id_productos =").append(id).append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			if(rs.next()){
				bean = new productosBeans();
				bean.setId_productos(rs.getInt("id_productos"));
				bean.setId_subcategorias(rs.getInt("id_subcategorias"));
				bean.setNombre(rs.getString("nombre"));
				bean.setUrl(rs.getString("url"));
			}	
		} catch (SQLException sql) {
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return bean;
	}
	
	public void eliminarSubcategoria(int id){
		try {
			conn = this.getConnectionMYSQL();
			query.append("DELETE FROM ").append("productos")
			.append(" WHERE id_productos =").append(id).append(";");
			conn.prepareStatement(query.toString());
			ps.executeUpdate();
	    }catch (SQLException sql) {	    	
	    }finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}
	
	public void insertaProducto(productosBeans bean){
		try {
			conn = this.getConnectionMYSQL();
			query.append("INSERT INTO ").append("productos")
			.append(" VALUES(").append(bean.getId_productos())
			.append(",").append(bean.getId_subcategorias())
			.append(",'").append(bean.getNombre()).append("'")
			.append(",'").append(bean.getUrl()).append("'")
			.append(")").append(";");
			conn.prepareStatement(query.toString());
			ps.executeUpdate();
	    }catch (SQLException sql) {	    	
	    }finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}
	
	public ArrayList<Object> numeroDeProductosPorMarca(){
			ArrayList<Object> lista = new ArrayList<Object>();
			Object[] bean ;
			String queryy;
			try {
				conn = this.getConnectionMYSQL();
				queryy = "SELECT  nombre, numeroProductos " +
                " FROM (SELECT "+
                " Nombre,"+
                " id_marca AS marca,"+
                " (select count(id_productos) from productos where id_subcategorias in("+
                " select id_subcategorias from subcategorias where id_categorias in("+
                " select id_categorias from categorias where id_catalogos in("+
                " select id_catalogos from catalogos where id_marca = marca"+
                " )))) AS numeroProductos"+
                " FROM MARCA "+
                " GROUP by id_marca)  AS NumProdPorMarca";
				conn.prepareStatement(queryy);
				rs = ps.executeQuery();
				while (rs.next()){
					bean =  new Object[2];
					bean[0] = rs.getString("nombre");
					bean[1] = rs.getInt("numeroProductos");
					lista.add(bean);
				}
			} catch (SQLException sql) {
			} finally {
				close(conn);
				close(ps);
				close(rs);
			}
			return lista;	
		}

}
